﻿using MySql.Data.MySqlClient;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Text;
using XuanFe.Database.Model;

namespace XuanFe.Database.DAL
{
    public class StaffLogDAL
    {
        private const string SQL_SELECT_WS_STAFF_LOG = "select SFL_LOG_ID as LOG_ID,SFL_STAFF_ID as STAFF_ID,DATE_FORMAT(SFL_OPERATE_TIME,'%Y-%m-%d %H:%i:%S') as OPERATE_TIME,SFL_MODULE_ID as MODULE_ID,SFL_REMARK as REMARK,(select STF_STAFF_NAME from WS_STAFF where STF_STAFF_ID=SFL_STAFF_ID) as STAFF_NAME,(select MOD_MODULE_NAME from WS_MODULE where MOD_MODULE_ID=SFL_MODULE_ID) as MODULE_NAME from WS_STAFF_LOG where 1=1";
        private const string SQL_INSERT_WS_STAFF_LOG = "insert into WS_STAFF_LOG(SFL_LOG_ID,SFL_STAFF_ID,SFL_OPERATE_TIME,SFL_MODULE_ID,SFL_REMARK) values(@LogId,@StaffId,@OperateTime,@ModuleId,@Remark)";
        private const string SQL_COUNT_WS_STAFF_LOG = "select count(SFL_LOG_ID) from WS_STAFF_LOG where 1=1";

        private const string SQL_WHERE_STAFF_ID = " and SFL_STAFF_ID=@StaffId ";
        private const string SQL_WHERE_REMARK_LIKE = " and SFL_REMARK like @Remark ";
        private const string SQL_WHERE_OPERATE_TIME = " and SFL_OPERATE_TIME between @StartTime and @EndTime ";

        private const string SQL_ORDER_BY_OPERATE_TIME = " order by OPERATE_TIME ";

        private const string PARM_LOG_ID = "@LogId";
        private const string PARM_STAFF_ID = "@StaffId";
        private const string PARM_OPERATE_TIME = "@OperateTime";
        private const string PARM_MODULE_ID = "@ModuleId";
        private const string PARM_REMARK = "@Remark";
        private const string PARM_START_TIME = "@StartTime";
        private const string PARM_END_TIME = "@EndTime";

        private static StaffLogDAL instance = new StaffLogDAL();
        /// <summary>
        /// 初始化实例
        /// </summary>
        /// <returns></returns>
        public static StaffLogDAL Instance
        {
            get
            {
                return instance;
            }
        }
        public DataSet GetDataSet(QueryLogModel model)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_SELECT_WS_STAFF_LOG);
            List<MySqlParameter> parms = new List<MySqlParameter>();

            if (model != null)
            {
                if (model.QueryStaffId)
                {
                    sbSQL.Append(SQL_WHERE_STAFF_ID);
                    parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.StaffId });
                }
                if (model.QueryKeyword)
                {
                    sbSQL.Append(SQL_WHERE_REMARK_LIKE);
                    parms.Add(new MySqlParameter(PARM_REMARK, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = string.Format("%{0}%", model.Keyword) });
                }
                if (model.QueryOperateTime)
                {
                    sbSQL.Append(SQL_WHERE_OPERATE_TIME);
                    parms.Add(new MySqlParameter(PARM_START_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.StartTime });
                    parms.Add(new MySqlParameter(PARM_END_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.EndTime });
                }
            }
            sbSQL.Append(SQL_ORDER_BY_OPERATE_TIME);

            return MySQLHelper.Instance.ExecuteDataSet(MySQLHelper.Instance.ConnectionString, CommandType.Text, sbSQL.ToString(), parms.ToArray());
        }
        public int Insert(DbTransaction trans, StaffLogModel model)
        {
            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_LOG_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Id });
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.StaffId });
            parms.Add(new MySqlParameter(PARM_OPERATE_TIME, MySqlDbType.DateTime) { Direction = ParameterDirection.Input, Value = model.OperateTime });
            parms.Add(new MySqlParameter(PARM_MODULE_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.ModuleId });
            parms.Add(new MySqlParameter(PARM_REMARK, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = model.Remark });

            return MySQLHelper.Instance.ExecuteNonQuery(trans, CommandType.Text, SQL_INSERT_WS_STAFF_LOG, parms.ToArray());
        }
        public bool StaffUsing(string staffId)
        {
            StringBuilder sbSQL = new StringBuilder(SQL_COUNT_WS_STAFF_LOG);
            sbSQL.Append(SQL_WHERE_STAFF_ID);

            List<MySqlParameter> parms = new List<MySqlParameter>();
            parms.Add(new MySqlParameter(PARM_STAFF_ID, MySqlDbType.String) { Direction = ParameterDirection.Input, Value = staffId });

            return MySQLHelper.Instance.RecordIsExist(MySQLHelper.Instance.ConnectionString, sbSQL.ToString(), parms.ToArray());
        }
    }
}
